use super::TbUser;
use crate::{error::SqlxErr, store::get_pool};
use sqlx::{Row, SqliteConnection};

async fn edit_user_impl(
    conn: &mut SqliteConnection,
    id: i64,
    name: &str,
    value: &str,
) -> Result<(), SqlxErr> {
    let sql = format!("UPDATE tb_user SET {} = ? WHERE id = ?", name);

    sqlx::query(&sql).bind(value).bind(id).execute(conn).await?;

    Ok(())
}

pub async fn edit_user_all(
    id: i64,
    game_id: Option<&str>,
    game_name: Option<&str>,
    faction: Option<&str>,
    profession: Option<&str>,
    character: Option<&str>,
    notes: Option<&str>,
) -> Result<(), SqlxErr> {

    sqlx::query(r#"
        UPDATE tb_user 
        SET game_id = ?, game_name = ?, faction = ?, profession = ?, character = ?, notes = ?
        WHERE id = ?
    "#)
    .bind(game_id)
    .bind(game_name)
    .bind(faction)
    .bind(profession)
    .bind(character)
    .bind(notes)
    .bind(id)
    .execute( get_pool() )
    .await?;

    Ok(())
}

pub async fn edit_user(
    id: i64,
    game_id: Option<&str>,
    game_name: Option<&str>,
    faction: Option<&str>,
    profession: Option<&str>,
    character: Option<&str>,
    notes: Option<&str>,
) -> Result<(), SqlxErr> {
    let mut tx = get_pool().begin().await?;

    if let Some(game_id) = game_id {
        edit_user_impl( &mut tx, id, "game_id", game_id).await?;
    }
    if let Some(game_name) = game_name {
        edit_user_impl(&mut tx, id, "game_name", game_name).await?;
    }
    if let Some(faction) = faction {
        edit_user_impl(&mut tx, id, "faction", faction).await?;
    }
    if let Some(profession) = profession {
        edit_user_impl(&mut tx, id, "profession", profession).await?;
    }
    if let Some(character) = character {
        edit_user_impl(&mut tx, id, "character", character).await?;
    }
    if let Some(notes) = notes {
        edit_user_impl(&mut tx, id, "notes", notes).await?;
    }

    tx.commit().await?;

    Ok(())
}

pub async fn create_user(
    game_id: &str,
    game_name: &str,
    faction: &str,
    profession: &str,
    character: &str,
    notes: &str,
) -> Result<i64, SqlxErr> {
    let ret = sqlx::query(
        r#"
        INSERT INTO tb_user (
            game_id, game_name, faction, profession, character, notes
        ) VALUES (?, ?, ?, ?, ?, ?)
    "#,
    )
    .bind(game_id)
    .bind(game_name)
    .bind(faction)
    .bind(profession)
    .bind(character)
    .bind(notes)
    .execute(get_pool())
    .await?;

    Ok(ret.last_insert_rowid())
}

pub async fn query_all_user() -> Result<Box<[TbUser]>, SqlxErr> {
    let rows = sqlx::query(
        r#"
        SELECT id, game_id, game_name, faction, profession, character, notes 
        FROM tb_user 
    "#,
    )
    .fetch_all(get_pool())
    .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let user = TbUser {
            id: row.get(0),
            game_id: row.get(1),
            game_name: row.get(2),
            faction: row.get(3),
            profession: row.get(4),
            character: row.get(5),
            notes: row.get(6),
        };
        vec.push(user);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn query_user(id: i64) -> Result<TbUser, SqlxErr> {
    let row = sqlx::query(
        r#"
        SELECT id, game_id, game_name, faction, profession, character, notes 
        FROM tb_user WHERE id = ?
    "#,
    )
    .bind(id)
    .fetch_one(get_pool())
    .await?;

    let user = TbUser {
        id: row.get(0),
        game_id: row.get(1),
        game_name: row.get(2),
        faction: row.get(3),
        profession: row.get(4),
        character: row.get(5),
        notes: row.get(6),
    };

    Ok(user)
}

pub async fn delete_user(conn: &mut SqliteConnection, id: i64) -> Result<(), SqlxErr> {
    sqlx::query(r#"DELETE FROM tb_user WHERE id = ?"#)
        .bind(id)
        .execute(conn)
        .await?;

    Ok(())
}
